﻿using System.Data.SQLite;
using System;
using System.IO;
using System.Diagnostics;
using CSharp.Libs.ExceptionHandling;
using CSharp.Libs.Koc.ItemClasses;
using System.Collections.ObjectModel;
using CSharp.Libs.Koc;
using System.Collections.Generic;
using System.Data;
using System.Collections;

namespace CSharp.Libs.Koc
{
    public partial class DatabaseOperations
    {
        #region declarations
        private Int64 _intPrevtickMinDifference = 60 * 50; //Minimum of 50 minutes differnce

        //private string _strCmdGetCountsSelect = " SELECT UserId, ServerId, NameInGame, ForeName, LastName, COUNT(NameInGame) AS [Count] ";
        private string _strCmdGetCountsSelect = " SELECT *, COUNT(NameInGame) AS [Count] ";
        private string _strSqlFromTblInformation = " FROM tblInformation ";
        private string _strSqlGroupUserIdServerId = " GROUP BY UserId, ServerId ";
        private string _strSqlSelectAsterisk = "SELECT * ";
        private string _strSqlOrderPrevTickDesc = " ORDER BY PrevTick DESC ";
        private string _strSqlLimit2 = " LIMIT 2 ";
        #endregion



        #region private functions
        #region save data
        /// <summary> saves data of a PlayerItem in the database</summary>
        /// <param name="PI"></param>
        /// <returns></returns>
        /// <history>created  23.08.2012</history>
        private bool _savePlayerItem(
            PlayerItem PI)
        {
            try
            {
                //prepare return value
                bool fSaveSuccessful = true;

                //Check if time difference is big enough
                if (!_timeDifferenceIsBigEnough(Convert.ToInt64(PI.UserId), PI.ServerId, PI.PrevTick))
                {
                    //create return value enum oder so
                    return false;
                }

                //calculate sums
                Int64 intSumGold = 0;
                Int64 intSumFood = 0;
                Int64 intSumWood = 0;
                Int64 intSumStone = 0;
                Int64 intSumOre = 0;
                Int64 intSumAetherstone = 0;

                foreach (KeyValuePair<string, CityItem> kvpCity in PI.Cities)
                {
                    CityItem CI = kvpCity.Value;

                    intSumGold += CI.Gold;
                    intSumFood += CI.Food.Amount;
                    intSumWood += CI.Wood.Amount;
                    intSumStone += CI.Stone.Amount;
                    intSumOre += CI.Ore.Amount;
                    intSumAetherstone += CI.Aetherstone.Amount;
                }

                
                //Create command SQL strings
                String strCmdInfoTable = String.Concat(
                    "INSERT INTO tblInformation(",
                    "UserId, FbUId, NameInGame, ForeName, LastName, ServerId, PrevTick, ",
                    "SumGold, SumFood, SumWood, SumStone, SumOre, SumAetherstone) VALUES (",
                    "@UserIdParam, @FbUIdParam, @NameParam, @ForeNameParam, @LastNameParam, @ServerIdParam, @PrevtickParam, ",
                    "@GoldParam, @FoodParam, @WoodParam, @StoneParam, @OreParam, @AetherstoneParam);",
                    Environment.NewLine,
                    "SELECT last_insert_rowid() AS RecordID;");
                String strCmdCityTable = String.Concat(
                    "INSERT INTO tblCities(",
                    "InformationID, CityId, Name, XCoord, YCoord, Tax, ",
                    "Gold, Food, Wood, Stone, Ore, Aetherstone) VALUES (",
                    "@InformationIDParam, @CityIdParam, @NameParam, @XCoordParam, @YCoordParam, @TaxParam, ",
                    "@GoldParam, @FoodParam, @WoodParam, @StoneParam, @OreParam, @AetherstoneParam);");

                //create information table command and add parameters
                SQLiteCommand slcInformation = new SQLiteCommand(strCmdInfoTable, _CON);
                slcInformation.Parameters.AddWithValue("@UserIdParam", Convert.ToInt64(PI.UserId));
                slcInformation.Parameters.AddWithValue("@FbUIdParam", Convert.ToInt64(PI.FbUId));
                slcInformation.Parameters.AddWithValue("@NameParam", PI.name);
                slcInformation.Parameters.AddWithValue("@ForeNameParam", PI.ForeName);
                slcInformation.Parameters.AddWithValue("@LastNameParam", PI.LastName);
                slcInformation.Parameters.AddWithValue("@ServerIdParam", PI.ServerId);
                slcInformation.Parameters.AddWithValue("@PrevtickParam", PI.PrevTick);
                slcInformation.Parameters.AddWithValue("@GoldParam", intSumGold);
                slcInformation.Parameters.AddWithValue("@FoodParam", intSumFood);
                slcInformation.Parameters.AddWithValue("@WoodParam", intSumWood);
                slcInformation.Parameters.AddWithValue("@StoneParam", intSumStone);
                slcInformation.Parameters.AddWithValue("@OreParam", intSumOre);
                slcInformation.Parameters.AddWithValue("@AetherstoneParam", intSumAetherstone);

                //submit command and receive newly created InformationID
                Int64 intInformationID = Convert.ToInt64(slcInformation.ExecuteScalar());

                //cycle through cities  to create single entries in city table
                foreach (KeyValuePair<string, CityItem> kvpCity in PI.Cities)
                {
                    //get CityItem
                    CityItem CI = kvpCity.Value;

                    //create city table command and add parameters
                    SQLiteCommand slcCity = new SQLiteCommand(strCmdCityTable, _CON);
                    slcCity.Parameters.AddWithValue("@InformationIDParam", intInformationID);
                    slcCity.Parameters.AddWithValue("@CityIdParam", CI.CityId);
                    slcCity.Parameters.AddWithValue("@NameParam", CI.Name);
                    slcCity.Parameters.AddWithValue("@XCoordParam", CI.XCoord);
                    slcCity.Parameters.AddWithValue("@YCoordParam", CI.YCoord);
                    slcCity.Parameters.AddWithValue("@TaxParam", CI.Tax);
                    slcCity.Parameters.AddWithValue("@GoldParam", CI.Gold);
                    slcCity.Parameters.AddWithValue("@FoodParam", CI.Food.Amount);
                    slcCity.Parameters.AddWithValue("@WoodParam", CI.Wood.Amount);
                    slcCity.Parameters.AddWithValue("@StoneParam", CI.Stone.Amount);
                    slcCity.Parameters.AddWithValue("@OreParam", CI.Ore.Amount);
                    slcCity.Parameters.AddWithValue("@AetherstoneParam", CI.Aetherstone.Amount);

                    //submit command and check if the row was added
                    if (slcCity.ExecuteNonQuery() != 1)
                    {
                        fSaveSuccessful = false;
                    }
                }

                //return returnvalue. ;)
                return fSaveSuccessful;
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>checks if the time difference between the last saved data of this 
        /// user / server configuration is big enough.
        /// </summary>
        /// <param name="prevtick"></param>
        /// <returns></returns>
        /// <history>created  20.08.2012</history>
        private bool _timeDifferenceIsBigEnough(
            Int64 UserId,
            Int64 ServerId,
            Int64 prevtick)
        {
            try
            {
                //should get the last entered prevtick for this User/server
                String strCMD = String.Concat(
                    "SELECT * FROM tblInformation WHERE UserId=",
                    UserId.ToString(),
                    " AND ServerId=",
                    ServerId.ToString(),
                    " ORDER BY PrevTick DESC LIMIT 1");
                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);

                using (SQLiteDataReader DR = CMD.ExecuteReader(System.Data.CommandBehavior.Default))
                {
                    if (DR.HasRows)
                    {
                        Int64 intLastPrevtick = _GetInt64(DR["PrevTick"]);
                        
                        //check difference
                        if (prevtick >= (intLastPrevtick + _intPrevtickMinDifference))
                        {
                            return true;
                        }
                    }
                    else
                    {
                        //No rows found means that this User/Server combination never was saved before, so that is ok.
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }
        #endregion

        #region read data
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// <history>created  04.09.2012</history>
        private ArrayList _getUserServerCounts()
        {
            try
            {
                //prepare return list
                ArrayList alInfos = new ArrayList();

                //generate command
                string strCmdGetCounts = _strCmdGetCountsSelect + _strSqlFromTblInformation + _strSqlGroupUserIdServerId;
                SQLiteCommand cmdGetCounts = new SQLiteCommand(strCmdGetCounts, _CON);

                //execute it
                //if (_CON.State != ConnectionState.Open) { _CON.Open(); }
                using (SQLiteDataReader drGetCounts = cmdGetCounts.ExecuteReader())
                {
                    //quit if no rows exist
                    if (!drGetCounts.HasRows) { return null; }

                    //read rows
                    while (drGetCounts.Read())
                    {
                        InfoItemBasic iib = new InfoItemBasic();
                        iib.Id = _GetInt64(drGetCounts["ID"]);
                        iib.UserId = _GetInt64(drGetCounts["UserId"]);
                        iib.ServerId = _GetInt64(drGetCounts["ServerId"]);
                        iib.NameInGame = drGetCounts["NameInGame"].ToString();
                        iib.Forename = drGetCounts["ForeName"].ToString();
                        iib.Lastname = drGetCounts["LastName"].ToString();
                        iib.Count = _GetInt64(drGetCounts["Count"]);

                        alInfos.Add(iib);
                    }
                }

                //return result
                if (alInfos.Count == 0) { return null; }
                return alInfos;

            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// <history>created  06.09.2012</history>
        private InfoItemBasic _getUserServerCountByUserServer(
            string UserId,
            string ServerId)
        {
            try
            {
                //prepare return list
                InfoItemBasic iib = null;

                //generate command
                string strCmdGetCountByUserServer = String.Concat(
                    _strCmdGetCountsSelect,
                    _strSqlFromTblInformation,
                    " WHERE UserId = ", UserId,
                    " AND ServerId = ", ServerId,
                    _strSqlGroupUserIdServerId);
                SQLiteCommand cmdGetCounts = new SQLiteCommand(strCmdGetCountByUserServer, _CON);

                //execute it
                //if (_CON.State != ConnectionState.Open) { _CON.Open(); }
                using (SQLiteDataReader drGetCounts = cmdGetCounts.ExecuteReader())
                {
                    //quit if no rows exist
                    if (!drGetCounts.HasRows) { return null; }

                    //read rows
                    while (drGetCounts.Read())
                    {
                        iib = new InfoItemBasic
                        {
                            UserId = _GetInt64(drGetCounts["UserId"]),
                            ServerId = _GetInt64(drGetCounts["ServerId"]),
                            NameInGame = drGetCounts["NameInGame"].ToString(),
                            Forename = drGetCounts["ForeName"].ToString(),
                            Lastname = drGetCounts["LastName"].ToString(),
                            Count = _GetInt64(drGetCounts["Count"])
                        };
                    }
                }

                //return result
                return iib;

            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="ServerId"></param>
        /// <returns></returns>
        /// <history>created  11.09.2012</history>
        private List<TblInformationItem> _getInformationsByUserServer(
            Int64 UserId,
            Int64 ServerId)
        {
            try
            {
                //create result object
                List<TblInformationItem> lstInformations = new List<TblInformationItem>();

                //build SQL string
                string strCmd = string.Concat(
                    _strSqlSelectAsterisk,
                    _strSqlFromTblInformation,
                    " WHERE UserId = ", UserId.ToString(),
                    " AND ServerId = ", ServerId.ToString(),
                    _strSqlOrderPrevTickDesc);

                //create command
                SQLiteCommand cmdInformations = new SQLiteCommand(strCmd, _CON);

                //execute it
                using (SQLiteDataReader drInformations = cmdInformations.ExecuteReader())
                {
                    //quit if no rows exist
                    if (!drInformations.HasRows) { return null; }

                    //read rows
                    while (drInformations.Read())
                    {
                        //create item
                        TblInformationItem tii = new TblInformationItem
                        {
                            ID = _GetInt64(drInformations["ID"]),
                            UserId = _GetInt64(drInformations["UserId"]),
                            FbUId = _GetInt64(drInformations["FbUId"]),
                            NameInGame = drInformations["NameInGame"].ToString(),
                            ForeName = drInformations["ForeName"].ToString(),
                            LastName = drInformations["LastName"].ToString(),
                            ServerId = _GetInt64(drInformations["ServerId"]),
                            PrevTick = _GetInt64(drInformations["PrevTick"]),
                            SumGold = _GetInt64(drInformations["SumGold"]),
                            SumFood = _GetInt64(drInformations["SumFood"]),
                            SumWood = _GetInt64(drInformations["SumWood"]),
                            SumStone = _GetInt64(drInformations["SumStone"]),
                            SumOre = _GetInt64(drInformations["SumOre"]),
                            SumAetherstone = _GetInt64(drInformations["SumAetherstone"])
                        };

                        //add new item to list
                        lstInformations.Add(tii);
                    }
                }

                //return result
                return lstInformations;
            }
            catch (Exception ex)
            {
                _KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return null;
        }

        private void _testGetDiffs()
        {
/*
SELECT         *
FROM            tblInformation
WHERE        (UserId = 3176423) AND (ServerId = 70)
ORDER BY PrevTick DESC
LIMIT 2
*/
        }
        #endregion
        #endregion



        #region interface
        #region properties
        public Int64 KocInformant_PrevtickMinDifference { get { return _intPrevtickMinDifference; } set { _intPrevtickMinDifference = value; } }
        #endregion

        #region methods
        /// <summary>saves PlayerItem to db.</summary>
        /// <param name="PI"></param>
        /// <returns></returns>
        /// <history>created  20.08.2012</history>
        public bool KocInformant_SavePlayerItem(
            PlayerItem PI)
        {
            return _savePlayerItem(PI);
        }

        /// <summary>reads database to get a list with all available user / server 
        /// combinations including a count of how many data sets of those combos are available</summary>
        /// <returns></returns>
        /// <history>created  27.08.2012</history>
        public ArrayList KocInformant_GetUserServerCounts()
        {
            return _getUserServerCounts();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="?"></param>
        /// <returns></returns>
        /// <history>created  06.09.2012</history>
        public InfoItemBasic KocInformant_GetUserServerCountByUserServer(
            string UserId,
            string ServerId)
        {
            return _getUserServerCountByUserServer(UserId, ServerId);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="ServerId"></param>
        /// <returns></returns>
        /// <history>created  11.09.2012</history>
        public List<TblInformationItem> KocInformant_GetInformationsByUserServer(
            Int64 UserId,
            Int64 ServerId)
        {
            return _getInformationsByUserServer(UserId, ServerId);
        }
        #endregion
        #endregion
    }
}
